var express = require('express');
var router = express.Router();

//加载mysql数据库
let mysql = require("mysql");
//配置mysql数据库
let config = {
  host: "localhost", //数据库连接服务
  port: 3306, //数据库端口
  user: "root", //数据库用户名
  password: "root", //数据库密码
  database: "student", //选择要操作的库房
}

//连接数据库
let connection = mysql.createConnection(config);

//判断是否连接成功
connection.connect((err) => {
  if (err) {
    console.log("连接数据库失败")
  } else {
    console.log("数据库连接成功")
  }
})



/* 渲染首页. */
router.get('/index', function (req, res, next) {
  //查询数据
  let sql = `select * from stu_score`;
  connection.query(sql, (err, doc) => {
    if (err) {
      console.log("查询失败")
    } else {
      //console.log(doc);
      res.render("index.html", {
        doc: doc
      });
    }
  })
});

/* 渲染添加页. */
router.get('/add', function (req, res, next) {
  res.render('add.html')
});


/* 添加页处理表单数据. */
router.post('/add', (req, res) => {
  let {
    name,
    sex,
    age
  } = req.body
  let sql = `insert into stu_score (name,sex,age) value ("${name}","${sex}",${age})`
  console.log(sql);
  connection.query(sql, (err) => {
    if (err) {
      res.render("wait.html", {
        wait: 2, //等待时间
        content: "添加失败",
        href: "/add"
      })
    } else {
      res.render("wait.html", {
        wait: 2, //等待时间
        content: "添加成功",
        href: "/index"
      })
    }
  })
})

/* 删除页处理表单数据. */
router.get('/del', (req, res) => {
  let ID = req.query.ID;
  let sql = `delete from stu_score where id=${ID}`
  connection.query(sql, (err) => {
    if (err) {
      res.render("wait.html", {
        wait: 2, //等待时间
        content: "删除失败",
        href: "/index"
      })
    } else {
      res.render("wait.html", {
        wait: 2, //等待时间
        content: "删除成功",
        href: "/index"
      })
    }
  })
})

/*修改页渲染表单数据. */
router.get("/edit", (req, res) => {
  //查询数据
  let ID = req.query.ID;
  let sql = `select * from stu_score where ID=${ID}`;
  connection.query(sql, (err, doc) => {
    if (err) {
      console.log("查询数据失败")
    } else {
      console.log(doc);
      res.render("edit.html", {
        result: doc
      })
    }

  })

})

/* 修改页处理表单数据. */
router.post('/edit', (req, res) => {
  let {name,sex,age,ID} = req.body
  let sql = `update stu_score set name="${name}",sex="${sex}",age=${age} where ID=${ID}`
  connection.query(sql, (err, doc) => {
    if (err) {
      res.render("wait.html", {
        wait: 2, //等待时间
        content: "修改失败",
        href: "/edit"
      })
    } else {
      res.render("wait.html", {
        wait: 2, //等待时间
        content: "修改成功",
        href: "/index"
      })
    }
  })
})
module.exports = router;